import re
import time
from flask import request
from flask_restful import abort
from flask_login import current_user, login_user
from funcy import project
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.exc import IntegrityError
from funcy import partial

from redash import models, limiter
from redash.permissions import (
    require_default,
)
from redash.handlers.base import (
    BaseResource,
    require_fields,
    get_object_or_404,
    paginate,
    order_results as _order_results,
)

from redash.authentication.account import (
    invite_link_for_user,
    send_invite_email,
    send_password_reset_email,
    send_verify_email,
)
from redash.settings import parse_boolean
from redash import settings

class EmployeeChangeNewResource(BaseResource):
    def get(self):
        result = models.EmployeeChange.get_id()
        return result[0]

    @require_default
    def post(self):
        req = request.get_json(force=True)
        require_fields(req, ("code","name"))

        employee = models.Employee(
        id=req["id"],
        org=self.current_org,

        bill_date = req["bill_date"],
        code = req["code"],
        name = req["name"],

        major =req["major"],
        area = req["area"],
        mobile = req["mobile"],
        userid = req["userid"],
        bytstatus = req["bytstatus"],
        status = req["status"],
        openseapool = req["openseapool"],
        employeeid = req["employeeid"],
        )
        attaches = models.Attaches(
        userid=req["userid"],
        org=self.current_org,
        type = "employee",
        annex =req["annex"],
        status =req["status"],
        sourcetype ="employee",
        sourceid =req["id"],
        )
        try:
            models.db.session.add(employee)
            models.db.session.add(attaches)
            models.db.session.commit()
        except IntegrityError as e:
            if "code" in str(e):
                abort(400, message="编号已存在.")
            abort(500)


class EmployeeChangeResource(BaseResource):
    def get(self, employeechange_id):
        result = models.EmployeeChange.get_by_id(employeechange_id)
        return result.to_dict()

    @require_default
    def post(self, employeechange_id):
        req = request.get_json(force=True)
        require_fields(req, ("code","name"))
            
        employeechange = models.EmployeeChange.get_by_id(employeechange_id)
        params = dict(bill_date=req["bill_date"], code=req["code"],name=req["name"],
                major=req["major"],area=req["area"],mobile=req["mobile"],bytstatus=req["bytstatus"],openseapool=req["openseapool"],
                employeeid = req["employeeid"])

        attaches = models.Attaches.get_by_id(employeechange_id,"employee")
        attachesparams = dict(annex=req["annex"])
        try:
            self.update_model(employeechange, params)
            self.update_model(attaches, attachesparams)
            models.db.session.commit()
        except IntegrityError as e:
            if "code" in str(e):
                abort(400, message="编号已存在.")
            abort(500)

class EmployeeChangeDeleteResource(BaseResource):
    @require_default
    def post(self, employeechange_id):
        employeechange =  models.EmployeeChange.get_by_id(employeechange_id)
        models.db.session.delete(employeechange)
        models.db.session.commit()

class EmployeeChangeListResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        page = req["page"]
        page_size = req["pagesize"]
        code = req["code"] if "code" in req else ""
        name = req["name"] if "name" in req else ""
        major = req["major"] if "major" in req else ""
        area = req["area"] if "area" in req else ""
        userid = str(req["userid"])
        id = str(req["id"]) if "area" in req else "0"
        bytstatus= req["bytstatus"] if "bytstatus" in req else ""
        status = str(req["status"]) if "status" in req else ""
        openseapool = str(req["openseapool"]) if "openseapool" in req else ""
        orderfield = req["orderfield"] if "orderfield" in req else "code"
        order = req["order"] if "order" in req else 0
        if orderfield=="code" :
            orderfield ="t1.code"
        elif orderfield=="name":
            orderfield ="t1.name"
        elif orderfield=="major":
            orderfield ="t1.major"
        elif orderfield=="area":
            orderfield ="t1.area"
        else:
            orderfield ="t1.code"

        

        strwhere ="t1.org_id=" +str(self.current_org.id)
        if code !="" :
            strwhere = strwhere + " and t1.code like '%"+code+"%'"
        if name !='' :
            strwhere = strwhere + " and t1.name like '%"+name+"%'"

        if userid !='':
            strwhere = strwhere + """ and  (t1.userid='""" + userid + """' or  t1.userid in (select  users.id from
                (with RECURSIVE T1 (keys,items) as
                (select   (items)->>'key' as keys,json_array_elements((items)->'children') as items,'0' as parentkeys
                 from   (with RECURSIVE le (keys,items,parentkeys) as
                (select   (items)->>'key' as keys,json_array_elements((items)->'children') as items,'0' as parentkeys
                 from   (select unnest(details) items From orgstruct where orgstruct_enum='admin' and expire_date is null) AA
                 union all
                 select (items)->>'key' as keys,json_array_elements((items)->'children') as items,keys as parentkeys from le)
                select le.* from le inner join users on to_number(le.keys, '9999999999999999999')= users.orgunit_id where users.id='""" + userid + """') AA
                 union all
                 select (items)->>'key' as keys,json_array_elements((items)->'children') as items,keys as parentkeys from T1)
                select * from T1
                union all
                (with RECURSIVE le (keys,items,parentkeys) as
                (select   (items)->>'key' as keys,json_array_elements((items)->'children') as items,'0' as parentkeys
                 from   (select unnest(details) items From orgstruct where orgstruct_enum='admin' and expire_date is null) AA
                 union all
                 select (items)->>'key' as keys,json_array_elements((items)->'children') as items,keys as parentkeys from le)
                select le.* from le inner join users on to_number(le.keys, '9999999999999999999')= users.orgunit_id where users.id='""" + userid + """'))T2
                inner join users on to_number((T2.items)->>'key', '9999999999999999999')=users.orgunit_id
                union all select users.id from public.users where users.orgunit_id in(select orgunit_id from public.users where id='""" + userid + """' )))"""
        strgetfield =""" t1.*,to_char(t1.bill_date,'YYYY-MM-DD') bill_dateformat,
                    case when t1.status=2 then '已审核' when t1.status=1 then '审核中' else '草稿' end strstatus"""

        tblname = """public.employeechange t1"""

        result = models.Enums.get_pagequery(tblname,strgetfield,orderfield,page_size,page,order,strwhere)
        return result[0]

